version 16
clear all
cd "D:\AGN 2023 RCFS"
set more off
capture log close _all
log using logs\BS.smcl, append name(mainlog)
local lognum = string(clock("$S_DATE $S_TIME","DMY hms"), "%tcCCYY_NN_DD_HH_MM_SS")
log using "logs\sublogs\BSLog_`lognum'.smcl", append name(secondarylog)

		clear all
	foreach fileyear in 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 {
		append using hmda`fileyear'
		keep unique_id outcome income loanamt bank state_code county_code year loan_type property_type loan_purpose occupancy purchaser_type zip pctloanamt appsocial census_tract_number ever60dq everdflt msa_md jumbo bbx_data fnma_data fhlmc_data origltvratiocalc ficoscoreoriginationcalc origintrtcalc amortizationtermcalc
	}

		gen byte originated=(outcome==1)
		gen byte approved=(outcome==2)

		egen otract=concat(pctloanamt state_code county_code census_tract_number), punct("-")
		gen tract = otract if census_tract_number~="NA" & county~=. & state~=.
		drop otract

		gen byte accept=approved+originated
		gen rat=loanamt/income

		keep if rat<.

		gen byte pdealcount=0
		gen byte pappcount=0
		gen byte pvoltotal=0
		gen byte pdeniedcount=0

		gen byte cdealcount=0
		gen byte cappcount=0
		gen byte cvoltotal=0
		gen byte cdeniedcount=0

		gen byte fdealcount=0
		gen byte fappcount=0
		gen byte fvoltotal=0
		gen byte fdeniedcount=0

		merge m:1 bank tract year using mktprev
			drop if _merge==2
			drop _merge

			replace pdealcount=dealcount
			replace pappcount=appcount
			replace pvoltotal=voltotal
			replace pdeniedcount=deniedcount

			drop dealcount appcount voltotal deniedcount

		merge m:1 bank tract year using mktcurr
			drop if _merge==2
			drop _merge

			replace cdealcount=dealcount
			replace cappcount=appcount
			replace cvoltotal=voltotal
			replace cdeniedcount=deniedcount
			
			drop dealcount appcount voltotal deniedcount

		merge m:1 bank tract year using mktfut
			drop if _merge==2
			drop _merge

			replace fdealcount=dealcount
			replace fappcount=appcount
			replace fvoltotal=voltotal
			replace fdeniedcount=deniedcount
	
			drop dealcount appcount voltotal deniedcount

			replace pdealcount=0 if pdealcount==. & year>2003
			replace pappcount=0 if pappcount==. & year>2003
			replace pvoltotal=0 if pvoltotal==. & year>2003
			replace pdeniedcount=0 if pdeniedcount==. & year>2003

			replace pdealcount=. if year==2003
			replace pappcount=. if year==2003
			replace pvoltotal=. if year==2003
			replace pdeniedcount=. if year==2003

			replace fdealcount=0 if fdealcount==. & year<2014
			replace fappcount=0 if fappcount==. & year<2014
			replace fvoltotal=0 if fvoltotal==. & year<2014
			replace fdeniedcount=0 if fdeniedcount==. & year<2014

			replace fdealcount=. if year==2014
			replace fappcount=. if year==2014
			replace fvoltotal=. if year==2014
			replace fdeniedcount=. if year==2014

			gen lcdealcount=log(1+cdealcount)

			gen lfdealcount=log(1+fdealcount)
			gen lfappcount=log(1+fappcount)
			gen lfvoltotal=log(1+fvoltotal)

			drop fappcount fvoltotal 
			
			******************************
			*Appendix Only Code <begin>***
			******************************
					gen lpdeniedcount = log(1+pdeniedcount)
					gen lfdeniedtoappcountrat = log(1+fdeniedcount)/lfappcount
					gen lcdeniedtoappcountrat = log(1+cdeniedcount)/log(1+cappcount)
					gen lpdeniedtoappcountrat = lpdeniedcount/log(1+pappcount)

					preserve
					
						keep bank year fnma_data fhlmc_data originated unique_id
						gen resid = substr(unique_id,strpos(unique_id,"-")+1,25)
						gen byte agency_code = real(substr(resid,1,strpos(resid,"-")-1))
							replace resid = strreverse(substr(resid,strpos(resid,"-")+1,25))
						gen long sequence_number = real(strreverse(substr(resid,1,strpos(resid,"-")-1)))
							replace resid = strreverse(substr(resid,strpos(resid,"-")+1,25))
						rename resid respondent_id
							drop unique_id
						merge 1:1 year respondent_id agency_code sequence_number using BS_Channels
							tab _merge originated, mi
							drop if _merge==2
							drop _merge
						keep if originated==1
							drop originated
						gen missingretailcode = 0
							replace missingretailcode = 1 if retail==.
							gen counter = 1
						collapse (sum) counter missingretailcode retail fnma_data fhlmc_data, by(bank year)
						gen fracretail = retail/(fnma_data + fhlmc_data)
						drop if fracretail==.
						gen retailbankyear = 1 if fracretail>=.5
							keep if retailbankyear==1
							keep year bank retailbankyear
								compress
						save RetailBankYears, replace
					
					restore
			******************************
			*Appendix Only Code <end>*****
			******************************
				
				drop fdeniedcount cdeniedcount pdeniedcount

				gen byte white=(appsocial==5)
				gen byte oo=(occupancy==1)
				gen byte conv=(loan_type==1)
				gen byte sing=(property_type==1 | property_type==.)
				gen byte purch=(loan_purpose==1)
			
				sort unique_id
				
					compress
	save hmda_applications, replace
	
	******************************
	*Appendix Only Code <begin>***
	******************************
		keep bank year bbx_data fnma_data fhlmc_data originated
		keep if originated==1
			drop originated
		
		gen no_data = 0
			replace no_data = 1 if bbx_data==0 & fnma_data==0 & fhlmc_data==0
		
		collapse (sum) bbx_data fnma_data fhlmc_data no_data, by(bank year)
		
		egen total = rowtotal(bbx_data fnma_data fhlmc_data no_data)
			gen datatotal = total - no_data
			gen fracnodata = 1 - datatotal/total

		replace bbx_data = bbx_data/datatotal
		replace fnma_data = fnma_data/datatotal
		replace fhlmc_data = fhlmc_data/datatotal
		
		drop no_data total datatotal
		
		gen originator_fannie = 1 if fnma_data<. & fnma_data>fhlmc_data & fnma_data>=bbx_data
		gen originator_freddie = 1 if fhlmc_data<. & fhlmc_data>=fnma_data & fhlmc_data>=bbx_data
		gen originator_equal = 1 if fnma_data== fhlmc_data & fnma_data>= bbx_data & fnma_data<.
		
		
		drop bbx_data fnma_data fhlmc_data fracnodata
		
		keep if originator_fannie==1 | originator_freddie==1 | originator_equal==1
		
		replace year = year + 1
		
			compress
	save hmda_originatortype, replace
	******************************
	*Appendix Only Code <end>*****
	******************************